package com.open.capacity.finance.service.impl;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.open.capacity.common.FileService;
import com.open.capacity.common.config.ServerConfig;
import com.open.capacity.common.util.XsteamUtil;
import com.open.capacity.finance.config.Globals;
import com.open.capacity.finance.dao.*;
import com.open.capacity.finance.dto.*;
import com.open.capacity.finance.entity.*;
import com.open.capacity.finance.service.*;
import com.open.capacity.finance.util.ExcelExportStatisticStyler;
import com.open.capacity.finance.util.POIExcelUtil;
import com.open.capacity.model.system.LoginAppUser;
import com.open.capacity.utils.BigDecimalUtil;
import com.open.capacity.utils.StringUtil;
import com.open.capacity.utils.SysUserUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.net.URL;
import java.util.*;
import java.util.stream.Collectors;


@Slf4j
@Service
public class PzImportServiceImpl implements PzImportService {

    @Autowired
    private FileService fileService;

    @Autowired
    private ServerConfig serverConfig;
    @Autowired
    private SourcePzService sourcePzService;


    @Autowired
    private SourceBaseKjkmDao sourceBaseKjkmDao;
    @Autowired
    private SourceBaseXmDao sourceBaseXmDao;
    @Autowired
    private SourceBaseCustomItemDao sourceBaseCustomItemDao;
    @Autowired
    private SourceBaseCustomValueDao sourceBaseCustomValueDao;

    @Autowired
    private SourceBaseKjkmExtDao sourceBaseKjkmExtDao;

    @Autowired
    private SourcePzDao sourcePzDao;


    @Autowired
    private SourceKmyeDao sourceKmyeDao;


    @Value("${file.uploadpath:/usr/local/files/upload}")
    private String fileuploadPath;


    @Transactional(rollbackFor = Exception.class)
    @Override
    public SourceExcelDto importPreview(MultipartFile multipartFile, Map<String, Object> config) throws Exception {
        LoginAppUser loginUser = SysUserUtil.getLoginAppUser();
        String category = config.get("category").toString();
        String kmcode1 = "710101,72010101";
        String kmcode2 = "710102,72010202";
        String kmcode3 = "71010101";
        String kmcode4 = "71010201";

        if (category.equals("2")) {
            kmcode1 = "720101,71010101";
            kmcode2 = "720102,71010202";
            kmcode3 = "72010101";
            kmcode4 = "72010201";
        }

        //校验数据
        SourceExcelDto result = validition(multipartFile, config);
        //存库
        //save(result);
        String report1FileUrl = createReport1(result, kmcode1, "report1_templete", "5_1");
        String report2FileUrl = createReport2(result, kmcode2, "report2_templete", "5_2");
        String report3FileUrl = createReport1(result, kmcode3, "report3_templete", "8_1");
        String report4FileUrl = createReport2(result, kmcode4, "report4_templete", "8_2");

        String report5FileUrl = createReport5(result, "report5_templete", "5");
        String report6FileUrl = createReport6(result, "report6_templete", "6");

        result.setReport1FileUrl(report1FileUrl);
        result.setReport2FileUrl(report2FileUrl);
        result.setReport3FileUrl(report3FileUrl);
        result.setReport4FileUrl(report4FileUrl);
        result.setReport5FileUrl(report5FileUrl);
        result.setReport6FileUrl(report6FileUrl);

        return result;
    }

    @Override
    public String importPreview2(MultipartFile multipartFile, Map<String, Object> params) throws Exception {
        String url = fileService.upload(multipartFile);
        SourceGLXML gl = (SourceGLXML) XsteamUtil.toBean(SourceGLXML.class, new URL(url));

        String companyId = "";
        int kjnd = gl.getPzList().get(0).getKjnd();
        Date now = new Date();

        //删除旧数据
//        sourceBaseKjkmDao.deleteByCompanyIdAndKjnd(companyId, kjnd);
//        sourceBaseKjkmExtDao.deleteByCompanyIdAndKjnd(companyId, kjnd);
//        sourcePzDao.deleteByCompanyIdAndKjnd(companyId, kjnd);
        sourceKmyeDao.deleteByCompanyIdAndKjnd(companyId, kjnd);


        //1. 记账凭证
        List<SourcePz> pzList = gl.getPzList();

        //1. 基础数据-会计科目导入
//        List<SourceBaseKjkm> baseKjkmList = gl.getBaseKjkmList();
//        baseKjkmList.forEach(t -> {
//            t.setCompanyId(companyId);
//            t.setKjnd(kjnd);
//            sourceBaseKjkmDao.save(t);
//        });
//        List<SourceBaseKjkmExt> baseKjkmExtList = gl.getBaseKjkmExtList();
//        baseKjkmExtList.forEach(t -> {
//            t.setCompanyId(companyId);
//            t.setKjnd(kjnd);
//            sourceBaseKjkmExtDao.save(t);
//        });

        //2. 记账凭证导入
//        if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(pzList)) {
//            pzList.forEach(t -> {
//                t.setCompanyId(companyId);
//                t.setUpdateTime(now);
//                sourcePzDao.save(t);
//            });
//        }

        //3. 科目余额及发生额导入
        List<SourceKmye> kmyeList = gl.getKmyeList();
//        kmyeList = fillYe(kmyeList);
        kmyeList.forEach(t -> {
            t.setCompanyId(companyId);
            t.setUpdateTime(now);
            sourceKmyeDao.save(t);
        });

        String errMsg = checkKmNcyeNegative();
        Globals.qcyeList = kmyeList;//缓存数据
        return errMsg;
    }

    @Override
    public int deleteQc() throws Exception {
        String companyId = "";
        //删除旧数据
//        sourcePzDao.deleteAll();
        sourceKmyeDao.deleteAll();

        return 1;
    }

    private List<SourceKmye> fillYe(List<SourceKmye> kmyeList) {
        HashMap<String, List<SourceKmye>> collect = kmyeList.stream().collect(Collectors.groupingBy(t -> t.getCompanyId() + "_" + t.getKjkmCode() + "_" + t.getKjnd() + "_" + t.getExt1() + "_" + t.getExt2() + "_" + t.getExt3() + "_" + t.getExt4() + "_" + t.getExt5() + "_" + t.getExt6(), HashMap::new, Collectors.toList()));
        for (Map.Entry<String, List<SourceKmye>> entry : collect.entrySet()) {
            entry.getValue().sort(Comparator.comparingInt(SourceKmye::getKjqj));
            entry.getValue().forEach(t -> {
                t.setYe(entry.getValue().stream().filter(c -> c.getKjqj().equals(12)).map(c -> c.getQm()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
            });
        }
        return kmyeList;
    }

    //创建报告1
    private String createReport1(SourceExcelDto data, String kjkmCode, String templeteName, String tableName) throws IOException {
        TemplateExportParams params = new TemplateExportParams("assets/" + templeteName + ".xlsx");
        params.setStyle(ExcelExportStatisticStyler.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("companyName", data.getCompanyName());
        map.put("ztName", data.getZtName());
        map.put("kjnd", data.getKjnd());

        List<BasePayDetail> list = packetBasePayDetail1(data, kjkmCode);

        map.put("list", list);

        String fileName = data.getCompanyName() + "_决算报告" + tableName;
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        FileOutputStream fos = new FileOutputStream(fileuploadPath + "/upload/" + fileName + ".xls");
        workbook.write(fos);
        fos.close();

        String url = null;
        File file = new File(fileuploadPath + "/upload/", fileName + ".xls");
        try {
            url = fileService.upload(file);
        } catch (Exception e) {
            log.error("upload file error:" + e.toString());
        }
        return url;
    }

    //创建报告1
    private String createReport2(SourceExcelDto data, String kjkmCode, String templeteName, String tableName) throws IOException {
        TemplateExportParams params = new TemplateExportParams("assets/" + templeteName + ".xlsx");
        params.setStyle(ExcelExportStatisticStyler.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("companyName", data.getCompanyName());
        map.put("ztName", data.getZtName());
        map.put("kjnd", data.getKjnd());

        List<ProjectPayDetail> list = packetBasePayDetail2(data, kjkmCode);

        map.put("list", list);

        String fileName = data.getCompanyName() + "_决算报告" + tableName;
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        FileOutputStream fos = new FileOutputStream(fileuploadPath + "/upload/" + fileName + ".xls");
        workbook.write(fos);
        fos.close();

        String url = null;
        File file = new File(fileuploadPath + "/upload/", fileName + ".xls");
        try {
            url = fileService.upload(file);
        } catch (Exception e) {
            log.error("upload file error:" + e.toString());
        }
        return url;
    }

    //创建报告5
    private String createReport5(SourceExcelDto data, String templeteName, String tableName) throws IOException {
        TemplateExportParams params = new TemplateExportParams("assets/" + templeteName + ".xlsx");
        params.setStyle(ExcelExportStatisticStyler.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("companyName", data.getCompanyName());
        map.put("ztName", data.getZtName());
        map.put("kjnd", data.getKjnd());

        List<ProjectIncomePayDetail> list = packetProjectPayDetail5(data);

        map.put("list", list);

        String fileName = data.getCompanyName() + "_决算报告" + tableName;
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        FileOutputStream fos = new FileOutputStream(fileuploadPath + "/upload/" + fileName + ".xls");
        workbook.write(fos);
        fos.close();

        String url = null;
        File file = new File(fileuploadPath + "/upload/", fileName + ".xls");
        try {
            url = fileService.upload(file);
        } catch (Exception e) {
            log.error("upload file error:" + e.toString());
        }
        return url;
    }

    //创建报告6
    private String createReport6(SourceExcelDto data, String templeteName, String tableName) throws IOException {
        TemplateExportParams params = new TemplateExportParams("assets/" + templeteName + ".xlsx");
        params.setStyle(ExcelExportStatisticStyler.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("companyName", data.getCompanyName());
        map.put("ztName", data.getZtName());
        map.put("kjnd", data.getKjnd());

        List<ProjectIncomePayDetail> list = packetProjectPayDetail6(data);

        map.put("list", list);

        String fileName = data.getCompanyName() + "_决算报告" + tableName;
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        FileOutputStream fos = new FileOutputStream(fileuploadPath + "/upload/" + fileName + ".xls");
        workbook.write(fos);
        fos.close();

        String url = null;
        File file = new File(fileuploadPath + "/upload/", fileName + ".xls");
        try {
            url = fileService.upload(file);
        } catch (Exception e) {
            log.error("upload file error:" + e.toString());
        }
        return url;
    }


    //组装基本支出决算明细表
    private List<BasePayDetail> packetBasePayDetail1(SourceExcelDto data, String kjkmCode) {
        List<BasePayDetail> list = new ArrayList<>();
        String[] kmArr = kjkmCode.split(",");
        Map<String, List<SourceImport>> map1 = data.getList().stream().filter(t -> {
            boolean flag = false;
            for (String i : kmArr) {
                if (t.getKjkm().startsWith(i)) {
                    flag = true;
                }
            }
            return flag;
        }).collect(Collectors.groupingBy(u -> u.getZcgnfl()));
        BasePayDetail item;
        for (Map.Entry<String, List<SourceImport>> entry1 : map1.entrySet()) {
            String key1 = entry1.getKey();
            if (StringUtil.isEmpty(key1)) {
                continue;
            }
            String[] arrays1 = key1.split(" ");
            item = new BasePayDetail();
            item.setXmCode(arrays1[0]);
            item.setXmName(arrays1[1]);

            Map<String, List<SourceImport>> map2 = entry1.getValue().stream().collect(Collectors.groupingBy(u -> u.getBmjjfl().substring(0, 5)));
            for (Map.Entry<String, List<SourceImport>> entry2 : map2.entrySet()) {
                String key2 = entry2.getKey();
                if (StringUtil.isNotEmpty(key2)) {

                    if (key2.length() == 5) {
                        BigDecimal value2 = entry2.getValue().stream().map(t -> t.getJ().add(t.getD())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b));
                        switch (key2) {
                            case "30101":  //基本工资
                                item.setGzflJbgz(value2);
                                break;
                            case "30102":  //津贴补贴
                                item.setGzflJtbt(value2);
                                break;
                            case "30103": //奖金
                                item.setGzflJj(value2);
                                break;
                            case "30106": //伙食补助费
                                item.setGzflHsbzf(value2);
                                break;
                            case "30107": //绩效工资
                                item.setGzflJxgz(value2);
                                break;
                            case "30108": //机关事业单位基本养老保险缴费
                                item.setGzflJbyanglao(value2);
                                break;
                            case "30109": //职业年金缴费
                                item.setGzflZynjjf(value2);
                                break;
                            case "30110": //职工基本医疗保险缴费
                                item.setGzflJbyiliao(value2);
                                break;
                            case "30111": //公务员医疗补助缴费
                                item.setGzflYlbz(value2);
                                break;
                            case "30112": //其他社会保障缴费
                                item.setGzflShbz(value2);
                                break;
                            case "30113": //住房公积金
                                item.setGzflZfgjj(value2);
                                break;
                            case "30114": //医疗费
                                item.setGzflYlf(value2);
                                break;
                            case "30199": //其他工资福利支出
                                item.setGzflQtgzflzc(value2);
                                break;
                            case "30201": //办公费
                                item.setSpfwBgf(value2);
                                break;
                            case "30202": //印刷费
                                item.setSpfwYsf(value2);
                                break;
                            case "30203": //咨询费
                                item.setSpfwZxf(value2);
                                break;
                            case "30204": //手续费
                                item.setSpfwSxf(value2);
                                break;
                            case "30205": //水费
                                item.setSpfwSf(value2);
                                break;
                            case "30206": //电费
                                item.setSpfwDf(value2);
                                break;
                            case "30207": //邮电费
                                item.setSpfwYdf(value2);
                                break;
                            case "30208": //取暖费
                                item.setSpfwQnf(value2);
                                break;
                            case "30209": //物业管理费
                                item.setSpfwWyglf(value2);
                                break;
                            case "30211": //差旅费
                                item.setSpfwClf(value2);
                                break;
                            case "30212": // 因公出国（境）费用
                                item.setSpfwYgcgf(value2);
                                break;
                            case "30213": //维修（护）费
                                item.setSpfwWxf(value2);
                                break;
                            case "30214": //租赁费
                                item.setSpfwZlf(value2);
                                break;
                            case "30215": //会议费
                                item.setSpfwHyf(value2);
                                break;
                            case "30216": //培训费
                                item.setSpfwPxf(value2);
                                break;
                            case "30217": //公务接待费
                                item.setSpfwGwjdf(value2);
                                break;
                            case "30218": //专用材料费
                                item.setSpfwZyclf(value2);
                                break;
                            case "30224": //被装购置费
                                item.setSpfwBzgzf(value2);
                                break;
                            case "30225": //专用燃料费
                                item.setSpfwZyrlf(value2);
                                break;
                            case "30226": //劳务费
                                item.setSpfwLwf(value2);
                                break;
                            case "30227": //委托业务费
                                item.setSpfwWtywf(value2);
                                break;
                            case "30228": //工会经费
                                item.setSpfwGhjf(value2);
                                break;
                            case "30229": //福利费
                                item.setSpfwFlf(value2);
                                break;
                            case "30231": //公务用车运行维护费
                                item.setSpfwGwycyxwhf(value2);
                                break;
                            case "30239": //其他交通费用
                                item.setSpfwQtjtf(value2);
                                break;
                            case "30240": //税金及附加费用
                                item.setSpfwSjjfjf(value2);
                                break;
                            case "30299": //其他商品和服务支出
                                item.setSpfwQtsphfwzc(value2);
                                break;
                            case "30301": //离休费
                                item.setGrjtbzLxf(value2);
                                break;
                            case "30302": //退休费
                                item.setGrjtbzTxf(value2);
                                break;
                            case "30303": //退职（役）费
                                item.setGrjtbzTzf(value2);
                                break;
                            case "30304": //抚恤金
                                item.setGrjtbzFxf(value2);
                                break;
                            case "30305": //生活补助
                                item.setGrjtbzShbz(value2);
                                break;
                            case "30306": //救济费
                                item.setGrjtbzJjf(value2);
                                break;
                            case "30307": //医疗费补助
                                item.setGrjtbzYlbz(value2);
                                break;
                            case "30308": //助学金
                                item.setGrjtbzZxj(value2);
                                break;
                            case "30309": //奖励金
                                item.setGrjtbzJlj(value2);
                                break;
                            case "30310": //个人农业生产补贴
                                item.setGrjtbzGrnyscbt(value2);
                                break;
                            case "30311": //代缴社会保险费
                                item.setGrjtbzDjshbxf(value2);
                                break;
                            case "30399": //其他对个人和家庭的补助
                                item.setGrjtbzQtgrjtbz(value2);
                                break;
                            case "30701": //国内债务付息
                                item.setZwlxGnzwlx(value2);
                                break;
                            case "30702": //国外债务付息
                                item.setZwlxGwzwlx(value2);
                                break;
                            case "30703": //国内债务发行费用
                                item.setZwlxGnzwfxf(value2);
                                break;
                            case "30704": //国外债务发行费用
                                item.setZwlxGwzwfxf(value2);
                                break;
                            case "30901": //房屋建筑物购建
                                item.setZbxFwjzwgj1(value2);
                                break;
                            case "30902": //办公设备购置
                                item.setZbxBgsbgz1(value2);
                                break;
                            case "30903": //专用设备购置
                                item.setZbxZysbgz1(value2);
                                break;
                            case "30905": //基础设施建设
                                item.setZbxJcssjs1(value2);
                                break;
                            case "30906": //大型修缮
                                item.setZbxDxxs1(value2);
                                break;
                            case "30907": //信息网络及软件购置更新
                                item.setZbxXxwlrjgz1(value2);
                                break;
                            case "30908": //物资储备
                                item.setZbxWzcb1(value2);
                                break;
                            case "30913": //公务用车购置
                                item.setZbxGwycgz1(value2);
                                break;
                            case "30919": //其他交通工具购置
                                item.setZbxQtjtgjgz1(value2);
                                break;
                            case "30921": //文物和陈列品购置
                                item.setZbxWwhclpgz1(value2);
                                break;
                            case "30922": //无形资产购置
                                item.setZbxWxzcgz1(value2);
                                break;
                            case "30999": //其他基本建设支出
                                item.setZbxQtjbjs1(value2);
                                break;
                            case "31001": //房屋建筑物购建
                                item.setZbxFwjzwgj(value2);
                                break;
                            case "31002": //办公设备购置
                                item.setZbxBgsbgz(value2);
                                break;
                            case "31003": //专用设备购置
                                item.setZbxZysbgz(value2);
                                break;
                            case "31005": //基础设施建设
                                item.setZbxJcssjs(value2);
                                break;
                            case "31006": //大型修缮
                                item.setZbxDxxs(value2);
                                break;
                            case "31007": //信息网络及软件购置更新
                                item.setZbxXxwlrjgz(value2);
                                break;
                            case "31008": //物资储备
                                item.setZbxWzcb(value2);
                                break;
                            case "31013": //公务用车购置
                                item.setZbxGwycgz(value2);
                                break;
                            case "31019": //其他交通工具购置
                                item.setZbxQtjtgjgz(value2);
                                break;
                            case "31021": //文物和陈列品购置
                                item.setZbxWwhclpgz(value2);
                                break;
                            case "31022": //无形资产购置
                                item.setZbxWxzcgz(value2);
                                break;
                            case "31009": //土地补偿
                                item.setZbxTdbc(value2);
                                break;
                            case "31010": //安置补助
                                item.setZbxAzbz(value2);
                                break;
                            case "31011": //地上附着物和青苗补偿
                                item.setZbxDsfzwqmbc(value2);
                                break;
                            case "31012": //拆迁补偿
                                item.setZbxCqbc(value2);
                                break;
                            case "31099": //其他资本性支出
                                item.setZbxQtzbx(value2);
                                break;
                            case "31101": //资本金注入
                                item.setQybzZbxzr1(value2);
                                break;
                            case "31199": //其他对企业补助
                                item.setQybzQtjbjsqybz1(value2);
                                break;
                            case "31201": //资本金注入
                                item.setQybzZbxzr(value2);
                                break;
                            case "31203": //政府投资基金股权投资
                                item.setQybzZftzjjgqtz(value2);
                                break;
                            case "31204": //费用补贴
                                item.setQybzFybt(value2);
                                break;
                            case "31205": //利息补贴
                                item.setQybzLxbt(value2);
                                break;
                            case "31299": //其他对企业补助
                                item.setQybzQtqybz(value2);
                                break;
                            case "31302": //对社会保险基金补助
                                item.setShbzJjbz(value2);
                                break;
                            case "31303": //补充全国社会保障基金
                                item.setShbzBcqgshbzjj(value2);
                                break;
                            case "31304": //对机关事业单位职业年金的补助
                                item.setShbzJgsyzynjbz(value2);
                                break;
                            case "39906": //赠与
                                item.setOtherZy(value2);
                                break;
                            case "39907": //国家赔偿费用支出
                                item.setOtherGjpcfyzc(value2);
                                break;
                            case "39908":  //对民间非营利组织和群众性自治组织补贴
                                item.setOtherMjfylzzbt(value2);
                                break;
                            case "39999": //其他支出
                                item.setOtherQt(value2);
                                break;
                        }
                    }
                }
            }
            //基本工资福利合计
            item.setGzflTotal(BigDecimalUtil.getData(item.getGzflJbgz()).add(BigDecimalUtil.getData(item.getGzflJtbt())).add(BigDecimalUtil.getData(item.getGzflJj())).add(BigDecimalUtil.getData(item.getGzflHsbzf())).add(BigDecimalUtil.getData(item.getGzflJxgz())).add(BigDecimalUtil.getData(item.getGzflJbyanglao())).add(BigDecimalUtil.getData(item.getGzflZynjjf())).add(BigDecimalUtil.getData(item.getGzflJbyiliao())).add(BigDecimalUtil.getData(item.getGzflYlbz())).add(BigDecimalUtil.getData(item.getGzflShbz())).add(BigDecimalUtil.getData(item.getGzflZfgjj())).add(BigDecimalUtil.getData(item.getGzflYlf())).add(BigDecimalUtil.getData(item.getGzflQtgzflzc())));

            //商品服务支出合计
            item.setSpfwTotal(BigDecimalUtil.getData(item.getSpfwBgf()).add(BigDecimalUtil.getData(item.getSpfwYsf())).add(BigDecimalUtil.getData(item.getSpfwZxf())).add(BigDecimalUtil.getData(item.getSpfwSxf())).add(BigDecimalUtil.getData(item.getSpfwSf())).add(BigDecimalUtil.getData(item.getSpfwDf())).add(BigDecimalUtil.getData(item.getSpfwYdf())).add(BigDecimalUtil.getData(item.getSpfwQnf())).add(BigDecimalUtil.getData(item.getSpfwWyglf())).add(BigDecimalUtil.getData(item.getSpfwClf())).add(BigDecimalUtil.getData(item.getSpfwYgcgf())).add(BigDecimalUtil.getData(item.getSpfwWxf())).add(BigDecimalUtil.getData(item.getSpfwZlf())).add(BigDecimalUtil.getData(item.getSpfwHyf())).add(BigDecimalUtil.getData(item.getSpfwPxf())).add(BigDecimalUtil.getData(item.getSpfwGwjdf())).add(BigDecimalUtil.getData(item.getSpfwZyclf())).add(BigDecimalUtil.getData(item.getSpfwBzgzf())).add(BigDecimalUtil.getData(item.getSpfwZyrlf())).add(BigDecimalUtil.getData(item.getSpfwLwf())).add(BigDecimalUtil.getData(item.getSpfwWtywf())).add(BigDecimalUtil.getData(item.getSpfwGhjf())).add(BigDecimalUtil.getData(item.getSpfwFlf())).add(BigDecimalUtil.getData(item.getSpfwGwycyxwhf())).add(BigDecimalUtil.getData(item.getSpfwQtjtf())).add(BigDecimalUtil.getData(item.getSpfwSjjfjf())).add(BigDecimalUtil.getData(item.getSpfwQtsphfwzc())));

            //对个人和家庭的补助合计
            item.setGrjtbzTotal(BigDecimalUtil.getData(item.getGrjtbzLxf()).add(BigDecimalUtil.getData(item.getGrjtbzTxf())).add(BigDecimalUtil.getData(item.getGrjtbzTzf())).add(BigDecimalUtil.getData(item.getGrjtbzFxf())).add(BigDecimalUtil.getData(item.getGrjtbzShbz())).add(BigDecimalUtil.getData(item.getGrjtbzJjf())).add(BigDecimalUtil.getData(item.getGrjtbzYlbz())).add(BigDecimalUtil.getData(item.getGrjtbzZxj())).add(BigDecimalUtil.getData(item.getGrjtbzJlj())).add(BigDecimalUtil.getData(item.getGrjtbzGrnyscbt())).add(BigDecimalUtil.getData(item.getGrjtbzDjshbxf())).add(BigDecimalUtil.getData(item.getGrjtbzQtgrjtbz())));

            //债务合计
            item.setZwlxTotal(BigDecimalUtil.getData(item.getZwlxGnzwlx()).add(BigDecimalUtil.getData(item.getZwlxGwzwlx())).add(BigDecimalUtil.getData(item.getZwlxGnzwfxf())).add(BigDecimalUtil.getData(item.getZwlxGwzwfxf())));

            //资本性支出（基本建设）合计
            item.setZbxJbjstotal1(BigDecimalUtil.getData(item.getZbxFwjzwgj1()).add(BigDecimalUtil.getData(item.getZbxBgsbgz1())).add(BigDecimalUtil.getData(item.getZbxZysbgz1())).add(BigDecimalUtil.getData(item.getZbxJcssjs1())).add(BigDecimalUtil.getData(item.getZbxDxxs1())).add(BigDecimalUtil.getData(item.getZbxXxwlrjgz1())).add(BigDecimalUtil.getData(item.getZbxWzcb1())).add(BigDecimalUtil.getData(item.getZbxGwycgz1())).add(BigDecimalUtil.getData(item.getZbxQtjtgjgz1())).add(BigDecimalUtil.getData(item.getZbxWwhclpgz1())).add(BigDecimalUtil.getData(item.getZbxWxzcgz1())).add(BigDecimalUtil.getData(item.getZbxQtjbjs1())));

            //资本性支出合计
            item.setZbxTotal(BigDecimalUtil.getData(item.getZbxFwjzwgj()).add(BigDecimalUtil.getData(item.getZbxBgsbgz())).add(BigDecimalUtil.getData(item.getZbxZysbgz())).add(BigDecimalUtil.getData(item.getZbxJcssjs())).add(BigDecimalUtil.getData(item.getZbxDxxs())).add(BigDecimalUtil.getData(item.getZbxXxwlrjgz())).add(BigDecimalUtil.getData(item.getZbxWzcb())).add(BigDecimalUtil.getData(item.getZbxTdbc())).add(BigDecimalUtil.getData(item.getZbxAzbz())).add(BigDecimalUtil.getData(item.getZbxDsfzwqmbc())).add(BigDecimalUtil.getData(item.getZbxCqbc())).add(BigDecimalUtil.getData(item.getZbxGwycgz())).add(BigDecimalUtil.getData(item.getZbxQtjtgjgz())).add(BigDecimalUtil.getData(item.getZbxWwhclpgz())).add(BigDecimalUtil.getData(item.getZbxWxzcgz())).add(BigDecimalUtil.getData(item.getZbxQtzbx())));

            //对企业补助（基本建设）合计
            item.setQybzJbjstotal1(BigDecimalUtil.getData(item.getQybzZbxzr1()).add(BigDecimalUtil.getData(item.getQybzQtjbjsqybz1())));

            //对企业补助合计
            item.setQybzTotal(BigDecimalUtil.getData(item.getQybzZbxzr()).add(BigDecimalUtil.getData(item.getQybzZftzjjgqtz())).add(BigDecimalUtil.getData(item.getQybzFybt())).add(BigDecimalUtil.getData(item.getQybzLxbt())).add(BigDecimalUtil.getData(item.getQybzQtqybz())));

            //对社会保障基金补助合计
            item.setShbzTotal(BigDecimalUtil.getData(item.getShbzJjbz()).add(BigDecimalUtil.getData(item.getShbzBcqgshbzjj())).add(BigDecimalUtil.getData(item.getShbzJgsyzynjbz())));

            //其他支出合计
            item.setOtherTotal(BigDecimalUtil.getData(item.getOtherZy()).add(BigDecimalUtil.getData(item.getOtherGjpcfyzc())).add(BigDecimalUtil.getData(item.getOtherMjfylzzbt())).add(BigDecimalUtil.getData(item.getOtherQt())));

            //总计
            item.setTotal(BigDecimalUtil.getData(item.getOtherTotal()).add(BigDecimalUtil.getData(item.getShbzTotal())).add(BigDecimalUtil.getData(item.getQybzTotal())).add(BigDecimalUtil.getData(item.getZbxTotal())).add(BigDecimalUtil.getData(item.getZbxJbjstotal1())).add(BigDecimalUtil.getData(item.getZwlxTotal())).add(BigDecimalUtil.getData(item.getGrjtbzTotal())).add(BigDecimalUtil.getData(item.getSpfwTotal())).add(BigDecimalUtil.getData(item.getGzflTotal())));


            list.add(item);
            list.sort(Comparator.comparing(BasePayDetail::getXmCode));
        }
        return list;
    }

    //组装基本支出决算明细表
    private List<ProjectPayDetail> packetBasePayDetail2(SourceExcelDto data, String kjkmCode) {
        List<ProjectPayDetail> list = new ArrayList<>();
        String[] kmArr = kjkmCode.split(",");
        Map<String, List<SourceImport>> map1 = data.getList().stream().filter(t -> {
            boolean flag = false;
            for (String i : kmArr) {
                if (t.getKjkm().startsWith(i)) {
                    flag = true;
                }
            }
            return flag;
        }).collect(Collectors.groupingBy(u -> u.getZcgnfl() + "&" + (StringUtil.isEmpty(u.getYsxm()) ? "" : u.getYsxm())));
        ProjectPayDetail item;
        for (Map.Entry<String, List<SourceImport>> entry1 : map1.entrySet()) {
            String key1 = entry1.getKey();
            if (StringUtil.isEmpty(key1) || "&".equals(key1)) {
                continue;
            }
            String[] arrays = key1.split("&");
            String[] arraryXm = arrays[0].split(" ");

            item = new ProjectPayDetail();
            item.setXmCode(arraryXm[0]);
            item.setXmName(arraryXm[1]);

            if (arrays.length > 1) {
                String[] arraryYskm = arrays[1].split(" ");
                item.setXmCode2(arraryYskm[0]);
                if (arraryYskm.length > 1) {
                    item.setXmName2(arraryYskm[1]);
                }
            }

            Map<String, List<SourceImport>> map2 = entry1.getValue().stream().collect(Collectors.groupingBy(u -> u.getBmjjfl().substring(0, 5)));
            for (Map.Entry<String, List<SourceImport>> entry2 : map2.entrySet()) {
                String key2 = entry2.getKey();
                if (StringUtil.isNotEmpty(key2)) {
                    if (key2.length() == 5) {
                        BigDecimal value2 = entry2.getValue().stream().map(t -> t.getJ().add(t.getD())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b));
                        switch (key2) {
                            case "30101":  //基本工资
                                item.setGzflJbgz(value2);
                                break;
                            case "30102":  //津贴补贴
                                item.setGzflJtbt(value2);
                                break;
                            case "30103": //奖金
                                item.setGzflJj(value2);
                                break;
                            case "30106": //伙食补助费
                                item.setGzflHsbzf(value2);
                                break;
                            case "30107": //绩效工资
                                item.setGzflJxgz(value2);
                                break;
                            case "30108": //机关事业单位基本养老保险缴费
                                item.setGzflJbyanglao(value2);
                                break;
                            case "30109": //职业年金缴费
                                item.setGzflZynjjf(value2);
                                break;
                            case "30110": //职工基本医疗保险缴费
                                item.setGzflJbyiliao(value2);
                                break;
                            case "30111": //公务员医疗补助缴费
                                item.setGzflYlbz(value2);
                                break;
                            case "30112": //其他社会保障缴费
                                item.setGzflShbz(value2);
                                break;
                            case "30113": //住房公积金
                                item.setGzflZfgjj(value2);
                                break;
                            case "30114": //医疗费
                                item.setGzflYlf(value2);
                                break;
                            case "30199": //其他工资福利支出
                                item.setGzflQtgzflzc(value2);
                                break;
                            case "30201": //办公费
                                item.setSpfwBgf(value2);
                                break;
                            case "30202": //印刷费
                                item.setSpfwYsf(value2);
                                break;
                            case "30203": //咨询费
                                item.setSpfwZxf(value2);
                                break;
                            case "30204": //手续费
                                item.setSpfwSxf(value2);
                                break;
                            case "30205": //水费
                                item.setSpfwSf(value2);
                                break;
                            case "30206": //电费
                                item.setSpfwDf(value2);
                                break;
                            case "30207": //邮电费
                                item.setSpfwYdf(value2);
                                break;
                            case "30208": //取暖费
                                item.setSpfwQnf(value2);
                                break;
                            case "30209": //物业管理费
                                item.setSpfwWyglf(value2);
                                break;
                            case "30211": //差旅费
                                item.setSpfwClf(value2);
                                break;
                            case "30212": // 因公出国（境）费用
                                item.setSpfwYgcgf(value2);
                                break;
                            case "30213": //维修（护）费
                                item.setSpfwWxf(value2);
                                break;
                            case "30214": //租赁费
                                item.setSpfwZlf(value2);
                                break;
                            case "30215": //会议费
                                item.setSpfwHyf(value2);
                                break;
                            case "30216": //培训费
                                item.setSpfwPxf(value2);
                                break;
                            case "30217": //公务接待费
                                item.setSpfwGwjdf(value2);
                                break;
                            case "30218": //专用材料费
                                item.setSpfwZyclf(value2);
                                break;
                            case "30224": //被装购置费
                                item.setSpfwBzgzf(value2);
                                break;
                            case "30225": //专用燃料费
                                item.setSpfwZyrlf(value2);
                                break;
                            case "30226": //劳务费
                                item.setSpfwLwf(value2);
                                break;
                            case "30227": //委托业务费
                                item.setSpfwWtywf(value2);
                                break;
                            case "30228": //工会经费
                                item.setSpfwGhjf(value2);
                                break;
                            case "30229": //福利费
                                item.setSpfwFlf(value2);
                                break;
                            case "30231": //公务用车运行维护费
                                item.setSpfwGwycyxwhf(value2);
                                break;
                            case "30239": //其他交通费用
                                item.setSpfwQtjtf(value2);
                                break;
                            case "30240": //税金及附加费用
                                item.setSpfwSjjfjf(value2);
                                break;
                            case "30299": //其他商品和服务支出
                                item.setSpfwQtsphfwzc(value2);
                                break;
                            case "30301": //离休费
                                item.setGrjtbzLxf(value2);
                                break;
                            case "30302": //退休费
                                item.setGrjtbzTxf(value2);
                                break;
                            case "30303": //退职（役）费
                                item.setGrjtbzTzf(value2);
                                break;
                            case "30304": //抚恤金
                                item.setGrjtbzFxf(value2);
                                break;
                            case "30305": //生活补助
                                item.setGrjtbzShbz(value2);
                                break;
                            case "30306": //救济费
                                item.setGrjtbzJjf(value2);
                                break;
                            case "30307": //医疗费补助
                                item.setGrjtbzYlbz(value2);
                                break;
                            case "30308": //助学金
                                item.setGrjtbzZxj(value2);
                                break;
                            case "30309": //奖励金
                                item.setGrjtbzJlj(value2);
                                break;
                            case "30310": //个人农业生产补贴
                                item.setGrjtbzGrnyscbt(value2);
                                break;
                            case "30311": //代缴社会保险费
                                item.setGrjtbzDjshbxf(value2);
                                break;
                            case "30399": //其他对个人和家庭的补助
                                item.setGrjtbzQtgrjtbz(value2);
                                break;
                            case "30701": //国内债务付息
                                item.setZwlxGnzwlx(value2);
                                break;
                            case "30702": //国外债务付息
                                item.setZwlxGwzwlx(value2);
                                break;
                            case "30703": //国内债务发行费用
                                item.setZwlxGnzwfxf(value2);
                                break;
                            case "30704": //国外债务发行费用
                                item.setZwlxGwzwfxf(value2);
                                break;
                            case "30901": //房屋建筑物购建
                                item.setZbxFwjzwgj1(value2);
                                break;
                            case "30902": //办公设备购置
                                item.setZbxBgsbgz1(value2);
                                break;
                            case "30903": //专用设备购置
                                item.setZbxZysbgz1(value2);
                                break;
                            case "30905": //基础设施建设
                                item.setZbxJcssjs1(value2);
                                break;
                            case "30906": //大型修缮
                                item.setZbxDxxs1(value2);
                                break;
                            case "30907": //信息网络及软件购置更新
                                item.setZbxXxwlrjgz1(value2);
                                break;
                            case "30908": //物资储备
                                item.setZbxWzcb1(value2);
                                break;
                            case "30913": //公务用车购置
                                item.setZbxGwycgz1(value2);
                                break;
                            case "30919": //其他交通工具购置
                                item.setZbxQtjtgjgz1(value2);
                                break;
                            case "30921": //文物和陈列品购置
                                item.setZbxWwhclpgz1(value2);
                                break;
                            case "30922": //无形资产购置
                                item.setZbxWxzcgz1(value2);
                                break;
                            case "30999": //其他基本建设支出
                                item.setZbxQtjbjs1(value2);
                                break;
                            case "31001": //房屋建筑物购建
                                item.setZbxFwjzwgj(value2);
                                break;
                            case "31002": //办公设备购置
                                item.setZbxBgsbgz(value2);
                                break;
                            case "31003": //专用设备购置
                                item.setZbxZysbgz(value2);
                                break;
                            case "31005": //基础设施建设
                                item.setZbxJcssjs(value2);
                                break;
                            case "31006": //大型修缮
                                item.setZbxDxxs(value2);
                                break;
                            case "31007": //信息网络及软件购置更新
                                item.setZbxXxwlrjgz(value2);
                                break;
                            case "31008": //物资储备
                                item.setZbxWzcb(value2);
                                break;
                            case "31013": //公务用车购置
                                item.setZbxGwycgz(value2);
                                break;
                            case "31019": //其他交通工具购置
                                item.setZbxQtjtgjgz(value2);
                                break;
                            case "31021": //文物和陈列品购置
                                item.setZbxWwhclpgz(value2);
                                break;
                            case "31022": //无形资产购置
                                item.setZbxWxzcgz(value2);
                                break;
                            case "31009": //土地补偿
                                item.setZbxTdbc(value2);
                                break;
                            case "31010": //安置补助
                                item.setZbxAzbz(value2);
                                break;
                            case "31011": //地上附着物和青苗补偿
                                item.setZbxDsfzwqmbc(value2);
                                break;
                            case "31012": //拆迁补偿
                                item.setZbxCqbc(value2);
                                break;
                            case "31099": //其他资本性支出
                                item.setZbxQtzbx(value2);
                                break;
                            case "31101": //资本金注入
                                item.setQybzZbxzr1(value2);
                                break;
                            case "31199": //其他对企业补助
                                item.setQybzQtjbjsqybz1(value2);
                                break;
                            case "31201": //资本金注入
                                item.setQybzZbxzr(value2);
                                break;
                            case "31203": //政府投资基金股权投资
                                item.setQybzZftzjjgqtz(value2);
                                break;
                            case "31204": //费用补贴
                                item.setQybzFybt(value2);
                                break;
                            case "31205": //利息补贴
                                item.setQybzLxbt(value2);
                                break;
                            case "31299": //其他对企业补助
                                item.setQybzQtqybz(value2);
                                break;
                            case "31302": //对社会保险基金补助
                                item.setShbzJjbz(value2);
                                break;
                            case "31303": //补充全国社会保障基金
                                item.setShbzBcqgshbzjj(value2);
                                break;
                            case "31304": //对机关事业单位职业年金的补助
                                item.setShbzJgsyzynjbz(value2);
                                break;
                            case "39906": //赠与
                                item.setOtherZy(value2);
                                break;
                            case "39907": //国家赔偿费用支出
                                item.setOtherGjpcfyzc(value2);
                                break;
                            case "39908":  //对民间非营利组织和群众性自治组织补贴
                                item.setOtherMjfylzzbt(value2);
                                break;
                            case "39999": //其他支出
                                item.setOtherQt(value2);
                                break;
                        }
                    }
                }
            }
            //基本工资福利合计
            item.setGzflTotal(BigDecimalUtil.getData(item.getGzflJbgz()).add(BigDecimalUtil.getData(item.getGzflJtbt())).add(BigDecimalUtil.getData(item.getGzflJj())).add(BigDecimalUtil.getData(item.getGzflHsbzf())).add(BigDecimalUtil.getData(item.getGzflJxgz())).add(BigDecimalUtil.getData(item.getGzflJbyanglao())).add(BigDecimalUtil.getData(item.getGzflZynjjf())).add(BigDecimalUtil.getData(item.getGzflJbyiliao())).add(BigDecimalUtil.getData(item.getGzflYlbz())).add(BigDecimalUtil.getData(item.getGzflShbz())).add(BigDecimalUtil.getData(item.getGzflZfgjj())).add(BigDecimalUtil.getData(item.getGzflYlf())).add(BigDecimalUtil.getData(item.getGzflQtgzflzc())));

            //商品服务支出合计
            item.setSpfwTotal(BigDecimalUtil.getData(item.getSpfwBgf()).add(BigDecimalUtil.getData(item.getSpfwYsf())).add(BigDecimalUtil.getData(item.getSpfwZxf())).add(BigDecimalUtil.getData(item.getSpfwSxf())).add(BigDecimalUtil.getData(item.getSpfwSf())).add(BigDecimalUtil.getData(item.getSpfwDf())).add(BigDecimalUtil.getData(item.getSpfwYdf())).add(BigDecimalUtil.getData(item.getSpfwQnf())).add(BigDecimalUtil.getData(item.getSpfwWyglf())).add(BigDecimalUtil.getData(item.getSpfwClf())).add(BigDecimalUtil.getData(item.getSpfwYgcgf())).add(BigDecimalUtil.getData(item.getSpfwWxf())).add(BigDecimalUtil.getData(item.getSpfwZlf())).add(BigDecimalUtil.getData(item.getSpfwHyf())).add(BigDecimalUtil.getData(item.getSpfwPxf())).add(BigDecimalUtil.getData(item.getSpfwGwjdf())).add(BigDecimalUtil.getData(item.getSpfwZyclf())).add(BigDecimalUtil.getData(item.getSpfwBzgzf())).add(BigDecimalUtil.getData(item.getSpfwZyrlf())).add(BigDecimalUtil.getData(item.getSpfwLwf())).add(BigDecimalUtil.getData(item.getSpfwWtywf())).add(BigDecimalUtil.getData(item.getSpfwGhjf())).add(BigDecimalUtil.getData(item.getSpfwFlf())).add(BigDecimalUtil.getData(item.getSpfwGwycyxwhf())).add(BigDecimalUtil.getData(item.getSpfwQtjtf())).add(BigDecimalUtil.getData(item.getSpfwSjjfjf())).add(BigDecimalUtil.getData(item.getSpfwQtsphfwzc())));

            //对个人和家庭的补助合计
            item.setGrjtbzTotal(BigDecimalUtil.getData(item.getGrjtbzLxf()).add(BigDecimalUtil.getData(item.getGrjtbzTxf())).add(BigDecimalUtil.getData(item.getGrjtbzTzf())).add(BigDecimalUtil.getData(item.getGrjtbzFxf())).add(BigDecimalUtil.getData(item.getGrjtbzShbz())).add(BigDecimalUtil.getData(item.getGrjtbzJjf())).add(BigDecimalUtil.getData(item.getGrjtbzYlbz())).add(BigDecimalUtil.getData(item.getGrjtbzZxj())).add(BigDecimalUtil.getData(item.getGrjtbzJlj())).add(BigDecimalUtil.getData(item.getGrjtbzGrnyscbt())).add(BigDecimalUtil.getData(item.getGrjtbzDjshbxf())).add(BigDecimalUtil.getData(item.getGrjtbzQtgrjtbz())));

            //债务合计
            item.setZwlxTotal(BigDecimalUtil.getData(item.getZwlxGnzwlx()).add(BigDecimalUtil.getData(item.getZwlxGwzwlx())).add(BigDecimalUtil.getData(item.getZwlxGnzwfxf())).add(BigDecimalUtil.getData(item.getZwlxGwzwfxf())));

            //资本性支出（基本建设）合计
            item.setZbxJbjstotal1(BigDecimalUtil.getData(item.getZbxFwjzwgj1()).add(BigDecimalUtil.getData(item.getZbxBgsbgz1())).add(BigDecimalUtil.getData(item.getZbxZysbgz1())).add(BigDecimalUtil.getData(item.getZbxJcssjs1())).add(BigDecimalUtil.getData(item.getZbxDxxs1())).add(BigDecimalUtil.getData(item.getZbxXxwlrjgz1())).add(BigDecimalUtil.getData(item.getZbxWzcb1())).add(BigDecimalUtil.getData(item.getZbxGwycgz1())).add(BigDecimalUtil.getData(item.getZbxQtjtgjgz1())).add(BigDecimalUtil.getData(item.getZbxWwhclpgz1())).add(BigDecimalUtil.getData(item.getZbxWxzcgz1())).add(BigDecimalUtil.getData(item.getZbxQtjbjs1())));

            //资本性支出合计
            item.setZbxTotal(BigDecimalUtil.getData(item.getZbxFwjzwgj()).add(BigDecimalUtil.getData(item.getZbxBgsbgz())).add(BigDecimalUtil.getData(item.getZbxZysbgz())).add(BigDecimalUtil.getData(item.getZbxJcssjs())).add(BigDecimalUtil.getData(item.getZbxDxxs())).add(BigDecimalUtil.getData(item.getZbxXxwlrjgz())).add(BigDecimalUtil.getData(item.getZbxWzcb())).add(BigDecimalUtil.getData(item.getZbxTdbc())).add(BigDecimalUtil.getData(item.getZbxAzbz())).add(BigDecimalUtil.getData(item.getZbxDsfzwqmbc())).add(BigDecimalUtil.getData(item.getZbxCqbc())).add(BigDecimalUtil.getData(item.getZbxGwycgz())).add(BigDecimalUtil.getData(item.getZbxQtjtgjgz())).add(BigDecimalUtil.getData(item.getZbxWwhclpgz())).add(BigDecimalUtil.getData(item.getZbxWxzcgz())).add(BigDecimalUtil.getData(item.getZbxQtzbx())));

            //对企业补助（基本建设）合计
            item.setQybzJbjstotal1(BigDecimalUtil.getData(item.getQybzZbxzr1()).add(BigDecimalUtil.getData(item.getQybzQtjbjsqybz1())));

            //对企业补助合计
            item.setQybzTotal(BigDecimalUtil.getData(item.getQybzZbxzr()).add(BigDecimalUtil.getData(item.getQybzZftzjjgqtz())).add(BigDecimalUtil.getData(item.getQybzFybt())).add(BigDecimalUtil.getData(item.getQybzLxbt())).add(BigDecimalUtil.getData(item.getQybzQtqybz())));

            //对社会保障基金补助合计
            item.setShbzTotal(BigDecimalUtil.getData(item.getShbzJjbz()).add(BigDecimalUtil.getData(item.getShbzBcqgshbzjj())).add(BigDecimalUtil.getData(item.getShbzJgsyzynjbz())));

            //其他支出合计
            item.setOtherTotal(BigDecimalUtil.getData(item.getOtherZy()).add(BigDecimalUtil.getData(item.getOtherGjpcfyzc())).add(BigDecimalUtil.getData(item.getOtherMjfylzzbt())).add(BigDecimalUtil.getData(item.getOtherQt())));

            //总计
            item.setTotal(BigDecimalUtil.getData(item.getOtherTotal()).add(BigDecimalUtil.getData(item.getShbzTotal())).add(BigDecimalUtil.getData(item.getQybzTotal())).add(BigDecimalUtil.getData(item.getZbxTotal())).add(BigDecimalUtil.getData(item.getZbxJbjstotal1())).add(BigDecimalUtil.getData(item.getZwlxTotal())).add(BigDecimalUtil.getData(item.getGrjtbzTotal())).add(BigDecimalUtil.getData(item.getSpfwTotal())).add(BigDecimalUtil.getData(item.getGzflTotal())));
            list.add(item);
            list.sort(Comparator.comparing(ProjectPayDetail::getXmCode).thenComparing(ProjectPayDetail::getXmCode2));
        }
        return list;
    }


    //组装基本支出决算明细表
    private List<ProjectIncomePayDetail> packetProjectPayDetail5(SourceExcelDto data) {
        List<ProjectIncomePayDetail> list = new ArrayList<>();
//
//        Map<String, List<SourceImport>> map1 = data.getList().stream().filter(t ->
//                t.getKjkm().startsWith("710102") ||
//                        t.getKjkm().startsWith("600102") ||
//                        t.getKjkm().startsWith("660101") ||
//                        t.getKjkm().startsWith("660301") ||
//                        t.getKjkm().startsWith("660401") ||
//                        t.getKjkm().startsWith("660501") ||
//                        t.getKjkm().startsWith("660901")).collect(Collectors.groupingBy(u -> u.getZcgnfl() + "&" + (StringUtil.isEmpty(u.getYsxm()) ? "" : u.getYsxm())));

        Map<String, List<SourceImport>> map1 = data.getList().stream().filter(t -> t.getKjkm().startsWith("600101") || t.getKjkm().startsWith("6601") || t.getKjkm().startsWith("71010101") || t.getKjkm().startsWith("71010102")).collect(Collectors.groupingBy(u -> u.getZcgnfl() + "&" + (StringUtil.isEmpty(u.getYsxm()) ? "" : u.getYsxm())));

        ProjectIncomePayDetail item;
        for (Map.Entry<String, List<SourceImport>> entry1 : map1.entrySet()) {
            String key1 = entry1.getKey();
            if (StringUtil.isEmpty(key1) || "&".equals(key1)) {
                continue;
            }
            String[] arrays = key1.split("&");
            String[] arraryXm = arrays[0].split(" ");

            item = new ProjectIncomePayDetail();
            item.setXmCode(arraryXm[0]);
            item.setXmName(arraryXm[1]);

            if (arrays.length > 1) {
                String[] arraryYskm = arrays[1].split(" ");
                item.setXmCode2(arraryYskm[0]);
                if (arraryYskm.length > 1) {
                    item.setXmName2(arraryYskm[1]);
                }
            }

            Map<String, List<SourceImport>> map2 = entry1.getValue().stream().collect(Collectors.groupingBy(u -> u.getKjkm()));
            for (Map.Entry<String, List<SourceImport>> entry2 : map2.entrySet()) {
                String key2 = entry2.getKey();
                if (StringUtil.isNotEmpty(key2)) {
                    String[] arrays2 = key2.split(" ");
                    if (arrays2.length == 2) {
                        BigDecimal value2 = entry2.getValue().stream().map(t -> t.getJ().add(t.getD())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b));
                        String code = arrays2[0];
                        if (code.startsWith("600101")) {
                            code = "600101";
                        } else if (code.startsWith("6601")) {
                            code = "6601";
                        } else if (code.startsWith("71010101")) {
                            code = "71010101";
                        } else if (code.startsWith("71010102")) {
                            code = "71010102";
                        }
                        switch (code) {
                            case "71010101": //支出
                                item.setPayTotal(BigDecimalUtil.getData(item.getPayTotal()).add(value2));
                                item.setPayCzbk(value2);
                                break;
                            case "71010102": //其他支出
                                item.setPayTotal(BigDecimalUtil.getData(item.getPayTotal()).add(value2));
                                item.setPayQtzj(value2);
                                break;
                            case "6601":
                                item.setIncomeTotal(BigDecimalUtil.getData(item.getIncomeTotal()).add(value2));
                                item.setIncomeQtzj(value2);
                                break;
                            case "600101": //收入
                                item.setIncomeTotal(BigDecimalUtil.getData(item.getIncomeTotal()).add(value2));
                                item.setIncomeCzbk(value2);
                                break;
                        }
                    }
                }
            }

            list.add(item);
            list.sort(Comparator.comparing(ProjectIncomePayDetail::getXmCode));
        }
        return list;
    }

    //组装基本支出决算明细表
    private List<ProjectIncomePayDetail> packetProjectPayDetail6(SourceExcelDto data) {
        List<ProjectIncomePayDetail> list = new ArrayList<>();
//
//        Map<String, List<SourceImport>> map1 = data.getList().stream().filter(t ->
//                t.getKjkm().startsWith("710102") ||
//                        t.getKjkm().startsWith("600102") ||
//                        t.getKjkm().startsWith("660101") ||
//                        t.getKjkm().startsWith("660301") ||
//                        t.getKjkm().startsWith("660401") ||
//                        t.getKjkm().startsWith("660501") ||
//                        t.getKjkm().startsWith("660901")).collect(Collectors.groupingBy(u -> u.getZcgnfl() + "&" + (StringUtil.isEmpty(u.getYsxm()) ? "" : u.getYsxm())));

        Map<String, List<SourceImport>> map1 = data.getList().stream().filter(t -> t.getKjkm().startsWith("71010201") || t.getKjkm().startsWith("600102") || t.getKjkm().startsWith("71010202") || t.getKjkm().startsWith("71010203") || t.getKjkm().startsWith("6601")).collect(Collectors.groupingBy(u -> u.getZcgnfl() + "&" + (StringUtil.isEmpty(u.getYsxm()) ? "" : u.getYsxm())));


        ProjectIncomePayDetail item;
        for (Map.Entry<String, List<SourceImport>> entry1 : map1.entrySet()) {
            String key1 = entry1.getKey();
            if (StringUtil.isEmpty(key1) || "&".equals(key1)) {
                continue;
            }
            String[] arrays = key1.split("&");
            String[] arraryXm = arrays[0].split(" ");

            item = new ProjectIncomePayDetail();
            item.setXmCode(arraryXm[0]);
            item.setXmName(arraryXm[1]);

            if (arrays.length > 1) {
                String[] arraryYskm = arrays[1].split(" ");
                item.setXmCode2(arraryYskm[0]);
                if (arraryYskm.length > 1) {
                    item.setXmName2(arraryYskm[1]);
                }
            }

            Map<String, List<SourceImport>> map2 = entry1.getValue().stream().collect(Collectors.groupingBy(u -> u.getKjkm()));
            for (Map.Entry<String, List<SourceImport>> entry2 : map2.entrySet()) {
                String key2 = entry2.getKey();
                if (StringUtil.isNotEmpty(key2)) {
                    String[] arrays2 = key2.split(" ");
                    if (arrays2.length == 2) {
                        BigDecimal value2 = entry2.getValue().stream().map(t -> t.getJ().add(t.getD())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b));

                        String code = arrays2[0];
                        if (code.startsWith("600102")) {
                            code = "600102";
                        } else if (code.startsWith("6601")) {
                            code = "6601";
                        } else if (code.startsWith("71010201")) {
                            code = "71010201";
                        } else if (code.startsWith("71010202")) {
                            code = "71010202";
                        } else if (code.startsWith("71010203")) {
                            code = "71010203";
                        }
                        switch (code) {
                            case "71010201": //支出
                                item.setPayTotal(BigDecimalUtil.getData(item.getPayTotal()).add(value2));
                                item.setPayCzbk(value2);
                                break;
                            case "71010202": //其他支出
                            case "71010203": //其他支出
                                item.setPayTotal(BigDecimalUtil.getData(item.getPayTotal()).add(value2));
                                item.setPayQtzj(value2);
                                break;
                            case "6601":
                                item.setIncomeTotal(BigDecimalUtil.getData(item.getIncomeTotal()).add(value2));
                                item.setIncomeQtzj(value2);
                                break;
                            case "600102": //收入
                                item.setIncomeTotal(BigDecimalUtil.getData(item.getIncomeTotal()).add(value2));
                                item.setIncomeCzbk(value2);
                                break;
                        }
                    }
                }
            }

            list.add(item);
            list.sort(Comparator.comparing(ProjectIncomePayDetail::getXmCode));
        }
        return list;
    }


    /**
     * 单元格设置注释
     */
    private void setCellComment(Cell cell, String text, Sheet sheet, Workbook wb) {
        //设置颜色
        //setErrorCellStyle(cell, wb);

        ClientAnchor anchor = new XSSFClientAnchor();
        // 关键修改
        anchor.setDx1(0);
        anchor.setDx2(0);
        anchor.setDy1(0);
        anchor.setDy2(0);
        anchor.setCol1(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setCol2(cell.getColumnIndex() + 5);
        anchor.setRow2(cell.getRowIndex() + 6);
        // 结束
        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(anchor);
        comment.setString(new XSSFRichTextString(text));
        cell.setCellComment(comment);

    }

    /**
     * 设置红色字体
     */
    private void setErrorCellStyle(Cell cell, Workbook wb) {
        CellStyle errorStyle = cell.getCellStyle();
        Font font = wb.createFont(); //创建字体
        font.setFontName("宋体"); //字体
        font.setFontHeightInPoints((short) 11); //字体大小
        font.setBold(false); //是否加粗
        font.setColor(Font.COLOR_RED);
        errorStyle.setFont(font);
        cell.setCellStyle(errorStyle);
    }

    /**
     * 设置正常字体
     */
    private void setNomalCellStyle(Cell cell, Workbook wb) {
        CellStyle errorStyle = cell.getCellStyle();
        Font font = wb.getFontAt(errorStyle.getFontIndexAsInt());
        font.setColor(Font.COLOR_NORMAL);
        errorStyle.setFont(font);
        cell.setCellStyle(errorStyle);
    }

    /**
     * 校验excel数据
     */
    private SourceExcelDto validition(MultipartFile multipartFile, Map<String, Object> config) throws IOException {
        StringBuilder errMsg = new StringBuilder();
        InputStream inputStream = multipartFile.getInputStream();
        Workbook wb = WorkbookFactory.create(inputStream);

        Sheet sheet = wb.getSheetAt(0);
        // 获取最大行数(或者sheet.getLastRowNum())
        int rownum = sheet.getPhysicalNumberOfRows();

        Row header;
        Row row;
        Row row1 = sheet.getRow(1);
        Row row2 = sheet.getRow(2);
        Row row3 = sheet.getRow(3);
        Row row4 = sheet.getRow(4);
        Row row5 = sheet.getRow(5);

        //判断数据从第几行开始，表头有可能多一行
        int startRowIndex = 4;
        int headerRowIndex = 3;
        if ("日期".equals(row3.getCell(0).getStringCellValue())) {
            startRowIndex = 4;
            headerRowIndex = 3;
        } else if ("日期".equals(row4.getCell(0).getStringCellValue())) {
            startRowIndex = 5;
            headerRowIndex = 4;
        } else if ("日期".equals(row5.getCell(0).getStringCellValue())) {
            startRowIndex = 6;
            headerRowIndex = 5;
        }

        String zt = "";
        String titleperiod = "";
        String company = row1.getCell(0).getStringCellValue();
        if (company.contains("账套")) {
            zt = row1.getCell(0).getStringCellValue();
            titleperiod = row2.getCell(0).getStringCellValue();
        } else {
            zt = row2.getCell(0).getStringCellValue();
            titleperiod = row3.getCell(0).getStringCellValue();
        }
        String companyName = company;
        String ztName = zt;
        companyName = company.replace("单位：", "");
        ztName = ztName.replace("账套：", "");
        String kjnd = titleperiod.substring(0, 8).replace("期间：", "");

        SourceExcelDto result = new SourceExcelDto();
        result.setCompanyName(companyName);
        result.setZtName(ztName);
        result.setKjnd(kjnd);
        result.setPeriod(titleperiod);
        List<SourceImport> list = new ArrayList<>();
        SourceImport item;


        header = sheet.getRow(headerRowIndex);

        // 处理行数据
        for (int i = startRowIndex; i < rownum; i++) {
            row = sheet.getRow(i);
            // 遇到空行则结束
            if (row == null) {
                break;
            }

            String pzrq = POIExcelUtil.getString(row.getCell(0));//日期
            String pzh = POIExcelUtil.getString(row.getCell(1));//凭证号
            String zy = POIExcelUtil.getString(row.getCell(2));//摘要
//            if (zy.equals("执行款从“其他应付款”各明细调至受托代理负债各明细")) {
//                setCellComment(row.getCell(2), "错误注释，日期格式非法", sheet, wb);
//            }
            BigDecimal j = POIExcelUtil.getNumber(row.getCell(3));//借方金额
            BigDecimal d = POIExcelUtil.getNumber(row.getCell(4));//贷放金额

            String kjkm = POIExcelUtil.getString(row.getCell(5));//会计科目
            String dfkm = POIExcelUtil.getString(row.getCell(6));//对方科目

            String bmjjfl = null;
            String czbkzl = null;
            String czzb = null;
            String fylx = null;
            String wldx = null;
            String xm = null;
            String ysxm = null;
            String zcgnfl = null;
            String zclx = null;
            String jsfs = null;
//
            bmjjfl = POIExcelUtil.getString(row.getCell(7));//部门经济分类
//            String czzb = POIExcelUtil.getString(row.getCell(8));//财政指标
            fylx = POIExcelUtil.getString(row.getCell(9));//费用类型
            wldx = POIExcelUtil.getString(row.getCell(10));//往来单位
            xm = POIExcelUtil.getString(row.getCell(11));//项目
            ysxm = POIExcelUtil.getString(row.getCell(12));//预算项目
//            String zcgnfl = POIExcelUtil.getString(row.getCell(13));//支出功能分类
//            String zclx = POIExcelUtil.getString(row.getCell(14));//资产类型
//           String zffs = POIExcelUtil.getString(row.getCell(15));//支付方式
//           String zjxz = POIExcelUtil.getString(row.getCell(16));//资金性质
            String pjlx = POIExcelUtil.getString(row.getCell(17));//票据类型
            String pjh = POIExcelUtil.getString(row.getCell(18));//票据号
//            String hth = POIExcelUtil.getString(row.getCell(19));//合同号

            item = new SourceImport();
            item.setPzrq(pzrq);
            item.setPzh(pzh);
            item.setZy(zy);
            item.setJ(j);
            item.setD(d);
            item.setPjlx(pjlx);
            item.setPjh(pjh);
            item.setKjkm(kjkm);
            item.setDfkm(dfkm);

            getRowData(item, header, row);

            item.setBmjjfl(bmjjfl);
//            item.setCzbkzl(czbkzl);
//            item.setCzzb(czzb);
            item.setFylx(fylx);
//            item.setJsfs(jsfs);
            item.setWldw(wldx);
            item.setXm(xm);
            item.setYsxm(ysxm);
//            item.setZcgnfl(zcgnfl);
//            item.setZclx(zclx);

            list.add(item);
        }
        result.setList(list);

        errMsg.append(checkKmEqual(list));
        errMsg.append(checkKmNegative(list));
        errMsg.append(checkXmNegative(list));
        errMsg.append(checkCostBgtEcoEqual(list));
        errMsg.append(checkJjkmPercent(list));
        errMsg.append(checkKjkmExists(list));
        errMsg.append(checkKjkmSrExists(list));

        String fileName = result.getCompanyName() + "_校验结果";
//        OutputStream outputStream = new FileOutputStream(fileuploadPath + "/upload/" + fileName + ".xls");
//        wb.write(outputStream);
//
//        //释放资源
//        wb.close();
//        outputStream.close();

//        File file = new File(fileuploadPath + "/upload/", fileName + ".xls");
//        try {
//            String url = fileService.upload(file);
//            result.setErrorFileUrl(url);
//        } catch (Exception e) {
//            log.error("upload file error:" + e.toString());
//        }

        result.setNomalCount(list.size());
        result.setErrorCount(0);
        result.setErrorMsg(errMsg.toString());
        return result;
    }


    //检查5.1. 收入科目检查：
    //     a) 会计科目：  1011 = 800101	 总发生额。
    //     b) 会计科目    6001 = 4001
    //	   c) 会计科目    4601 = 6601
    //	   d) 会计科目    4604 = 6604
    //	   e) 会计科目    4901 = 6901
    public String checkKmEqual(List<SourceImport> pzList) {
        StringBuilder errMsg = new StringBuilder();
        List<SourceImport> list = pzList.stream().filter(t -> t.getKjkm().startsWith("1011") || t.getKjkm().startsWith("800101") || t.getKjkm().startsWith("4001") || t.getKjkm().startsWith("6001") || t.getKjkm().startsWith("4601") || t.getKjkm().startsWith("6601") || t.getKjkm().startsWith("4604") || t.getKjkm().startsWith("6604") || t.getKjkm().startsWith("4901") || t.getKjkm().startsWith("6904")).collect(Collectors.toList());


        HashMap<String, List<SourceImport>> kjkmList = list.stream().collect(Collectors.groupingBy(t -> t.getKjkm(), HashMap::new, Collectors.toList()));

        BigDecimal km1011 = BigDecimal.ZERO, km800101 = BigDecimal.ZERO;
        BigDecimal km4001 = BigDecimal.ZERO, km6001 = BigDecimal.ZERO;
        BigDecimal km4601 = BigDecimal.ZERO, km6601 = BigDecimal.ZERO;
        BigDecimal km4604 = BigDecimal.ZERO, km6604 = BigDecimal.ZERO;
        BigDecimal km4901 = BigDecimal.ZERO, km6901 = BigDecimal.ZERO;
        for (Map.Entry<String, List<SourceImport>> kjkm : kjkmList.entrySet()) {
            switch (kjkm.getKey().substring(0, 4)) {
                case "1011":
                    km1011 = km1011.add(kjkm.getValue().stream().map(t -> t.getJ().subtract(t.getD())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km1011 = km1011.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "8001":
                    km800101 = km800101.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km800101 = km800101.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "4001":
                    km4001 = km4001.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km4001 = km4001.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "6001":
                    km6001 = km6001.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km6001 = km6001.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "4601":
                    km4601 = km4601.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km4601 = km4601.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "6601":
                    km6601 = km6601.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km6601 = km6601.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "4604":
                    km4604 = km4604.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km4604 = km4604.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "6604":
                    km6604 = km6604.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km6604 = km6604.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "4901":
                    km4901 = km4901.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km4901 = km4901.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
                case "6901":
                    km6901 = km6901.add(kjkm.getValue().stream().map(t -> t.getD().subtract(t.getJ())).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    km6901 = km6901.add(Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().startsWith("6901")).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    break;
            }
        }
        if (km1011.compareTo(km800101) != 0) {
            errMsg.append("科目1011与800101余额存在差异\r\n");
        }
        if (km4001.compareTo(km6001) != 0) {
            errMsg.append("科目4001与6001余额存在差异\r\n");
        }
        if (km4601.compareTo(km6601) != 0) {
            errMsg.append("科目4601与6601余额存在差异\r\n");
        }
        if (km4604.compareTo(km6604) != 0) {
            errMsg.append("科目4604与6604余额存在差异\r\n");
        }
        if (km4901.compareTo(km6901) != 0) {
            errMsg.append("科目4901与6901余额存在差异\r\n");
        }

        return errMsg.toString();
    }


    /**
     * 年初余额校验
     */
    public String checkKmNcyeNegative() {
        List<SourceKmye> list = sourceKmyeDao.findNcyeNegative();
        StringBuilder errMsg = new StringBuilder();
        if (CollectionUtils.isNotEmpty(list)) {
            for (SourceKmye item : list) {
                errMsg.append("科目" + item.getKjkmCode() + "期初余额负数\r\n");
            }
        }

        return errMsg.toString();
    }

    //    5.2. 检查负数情况：
    //    a) 1,5,7开头的会计科目借方余额不能为负数，贷方余额不能有数   (排除1602，1702*)
    //    b) 2,3,4,6,8 开头的会计科目贷方余额不能为负数，借方余额不能有数 (排除3001*，3301*)
    public String checkKmNegative(List<SourceImport> pzList) {
        StringBuilder errMsg = new StringBuilder();
        List<SourceImport> list = pzList.stream().filter(t -> !t.getKjkm().startsWith("1602") && !t.getKjkm().startsWith("1702") && !t.getKjkm().startsWith("3001") && !t.getKjkm().startsWith("3301")).collect(Collectors.toList());


        HashMap<String, List<SourceImport>> kjkmList = list.stream().collect(Collectors.groupingBy(t -> t.getKjkm(), HashMap::new, Collectors.toList()));

        for (Map.Entry<String, List<SourceImport>> kjkm : kjkmList.entrySet()) {
            BigDecimal total = BigDecimal.ZERO;
            String kjkmCode = kjkm.getKey().split(" ")[0];
            if (kjkm.getKey().startsWith("1")) {
                kjkm.getValue().forEach(c -> {
                    BigDecimal ncye = Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().equals(kjkmCode)).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b));
                    c.setYe(ncye.add((c.getJ().subtract(c.getD()))));
                });
                total = total.add(kjkm.getValue().stream().map(SourceImport::getYe).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
            } else if (kjkm.getKey().startsWith("2")) {
                kjkm.getValue().forEach(c -> {
                    BigDecimal ncye = Globals.qcyeList.stream().filter(t -> t.getKjqj().equals(1) && t.getKjkmCode().equals(kjkmCode)).map(t -> t.getQc()).reduce(BigDecimal.ZERO, (a, b) -> a.add(b));
                    c.setYe(ncye.add((c.getD().subtract(c.getJ()))));
                });
                total = total.add(kjkm.getValue().stream().map(SourceImport::getYe).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
            }
            if (total.compareTo(BigDecimal.ZERO) < 0) {
                errMsg.append("科目" + kjkm.getKey() + "余额为负数\r\n");
            }
        }

        return errMsg.toString();
    }

    public String checkXmNegative(List<SourceImport> pzList) {
        StringBuilder errMsg = new StringBuilder();
        List<SourceImport> companyList = pzList.stream().filter(t -> (t.getKjkm().startsWith("8") && !t.getKjkm().startsWith("8001")) || t.getKjkm().startsWith("6") || t.getKjkm().startsWith("7")).collect(Collectors.toList());

        HashMap<String, List<SourceImport>> ysxmList = companyList.stream().collect(Collectors.groupingBy(t -> t.getYsxm(), HashMap::new, Collectors.toList()));

        for (Map.Entry<String, List<SourceImport>> ysxm : ysxmList.entrySet()) {
            String ysxmName = ysxm.getKey();
            BigDecimal total = BigDecimal.ZERO;
            for (SourceImport pz : ysxm.getValue()) {
                if (pz.getKjkm().startsWith("8")) {
                    total = total.add(pz.getJ());
                } else if (pz.getKjkm().startsWith("6")) {
                    total = total.add(pz.getD());
                } else if (pz.getKjkm().startsWith("7")) {
                    total = total.subtract(pz.getJ());
                }
            }
            if (total.compareTo(BigDecimal.ZERO) < 0) {
                errMsg.append("预算项目" + ysxmName + "  8开头的8*（剔除8001开头的8001*）的期初数 + 6开头的6* 的余额 - 7开头的7*余额为负数\r\n");
            }
        }

        return errMsg.toString();
    }

    /**
     * 1，	检查5001的费用类型对应7001/7101的部门支出经济分类：101-301,201-302,301-303。检查出差异凭证号
     */
    public String checkCostBgtEcoEqual(List<SourceImport> pzList) {
        StringBuilder errMsg = new StringBuilder();
        List<SourceImport> list = pzList.stream().filter(t -> t.getKjkm().startsWith("7101") || t.getKjkm().startsWith("5001")).collect(Collectors.toList());

        List<SourceImport> kjkm5001List = list.stream().filter(t -> t.getKjkm().startsWith("5001") && StringUtil.isNotEmpty(t.getFylx()) && (t.getFylx().startsWith("01") || t.getFylx().startsWith("02") || t.getFylx().startsWith("03"))).collect(Collectors.toList());
        List<SourceImport> kjkm7101List = list.stream().filter(t -> t.getKjkm().startsWith("7101") && StringUtil.isNotEmpty(t.getBmjjfl()) && (t.getBmjjfl().startsWith("301") || t.getBmjjfl().startsWith("302") || t.getBmjjfl().startsWith("303"))).collect(Collectors.toList());

        for (int i = 0; i < kjkm5001List.size(); i++) {
            for (int j = 0; j < kjkm7101List.size(); j++) {
                if (kjkm5001List.get(i).getJ().equals(kjkm7101List.get(j).getJ()) && kjkm5001List.get(i).getPzrq().equals(kjkm7101List.get(j).getPzrq()) && kjkm5001List.get(i).getPzh().equals(kjkm7101List.get(j).getPzh())) {
                    kjkm7101List.remove(j);
                    j--;

                    kjkm5001List.remove(i);
                    i--;
                    break;
                }
            }
        }
        HashMap<String, List<SourceImport>> map = kjkm7101List.stream().collect(Collectors.groupingBy(t -> t.getPzrq() + "_" + t.getPzh() + "_" + t.getZy(), HashMap::new, Collectors.toList()));
        List<SourceImport> kjkm7101List2 = new ArrayList<>();
        for (Map.Entry<String, List<SourceImport>> item : map.entrySet()) {
            BigDecimal amount = BigDecimal.ZERO;
            amount = amount.add(item.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
            item.getValue().get(0).setJ(amount);
            kjkm7101List2.add(item.getValue().get(0));
        }
        for (int i = 0; i < kjkm5001List.size(); i++) {
            for (int j = 0; j < kjkm7101List2.size(); j++) {
                if (kjkm5001List.get(i).getJ().equals(kjkm7101List2.get(j).getJ()) && kjkm5001List.get(i).getPzrq().equals(kjkm7101List2.get(j).getPzrq()) && kjkm5001List.get(i).getPzh().equals(kjkm7101List2.get(j).getPzh())) {
                    kjkm5001List.remove(i);
                    i--;
                    kjkm7101List2.remove(j);
                    j--;
                }
            }
        }

        if (CollectionUtils.isNotEmpty(kjkm5001List)) {
            errMsg.append("5001的费用类型对应7001/7101的部门支出经济分类：101-301,201-302,301-303。检查出差异凭证号" + "\r\n");
            for (SourceImport item : kjkm5001List) {
                errMsg.append("             _____5001凭证日期:" + item.getPzrq() + "_凭证号:" + item.getPzh() + "\r\n");
            }
        }
        if (CollectionUtils.isNotEmpty(kjkm7101List2)) {
            errMsg.append("5001的费用类型对应7001/7101的部门支出经济分类：101-301,201-302,301-303。检查出差异凭证号" + "\r\n");
            for (SourceImport item : kjkm7101List2) {
                errMsg.append("             _____7001凭证日期:" + item.getPzrq() + "_凭证号:" + item.getPzh() + "\r\n");
            }
        }
        return errMsg.toString();
    }

    //5.6  经济分类支出占比超过30%提醒
    //      710101，720101 会计科目
    //	  720101，720102 会计科目
    //	  30199/301，  30299/302，30399/303，30999/309，31099/310  占比必须小于30%；
    public String checkJjkmPercent(List<SourceImport> pzList) {
        StringBuilder errMsg = new StringBuilder();
        List<SourceImport> list = pzList.stream().filter(t -> (t.getKjkm().startsWith("710101") || t.getKjkm().startsWith("710102") || t.getKjkm().startsWith("720101") || t.getKjkm().startsWith("720102")) && (StringUtil.isNotEmpty(t.getBmjjfl()) && (t.getBmjjfl().startsWith("301") || t.getBmjjfl().startsWith("302") || t.getBmjjfl().startsWith("303") || t.getBmjjfl().startsWith("309") || t.getBmjjfl().startsWith("310")))).collect(Collectors.toList());


        HashMap<String, List<SourceImport>> kjkmList = list.stream().collect(Collectors.groupingBy(t -> t.getKjkm(), HashMap::new, Collectors.toList()));


        for (Map.Entry<String, List<SourceImport>> kjkm : kjkmList.entrySet()) {
            HashMap<String, List<SourceImport>> jjkmList = kjkm.getValue().stream().collect(Collectors.groupingBy(t -> t.getBmjjfl(), HashMap::new, Collectors.toList()));

            BigDecimal jj30199 = BigDecimal.ZERO, jj301 = BigDecimal.ZERO;
            BigDecimal jj30299 = BigDecimal.ZERO, jj302 = BigDecimal.ZERO;
            BigDecimal jj30399 = BigDecimal.ZERO, jj303 = BigDecimal.ZERO;
            BigDecimal jj30999 = BigDecimal.ZERO, jj309 = BigDecimal.ZERO;
            BigDecimal jj31099 = BigDecimal.ZERO, jj310 = BigDecimal.ZERO;
            for (Map.Entry<String, List<SourceImport>> ysxm : jjkmList.entrySet()) {
                if (ysxm.getKey().startsWith("301")) {
                    jj301 = jj301.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    if (ysxm.getKey().equals("30199")) {
                        jj30199 = jj30199.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    }
                } else if (ysxm.getKey().startsWith("302")) {
                    jj302 = jj302.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    if (ysxm.getKey().equals("30299")) {
                        jj30299 = jj30299.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    }
                } else if (ysxm.getKey().startsWith("303")) {
                    jj303 = jj303.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    if (ysxm.getKey().equals("30399")) {
                        jj30399 = jj30399.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    }
                } else if (ysxm.getKey().startsWith("309")) {
                    jj309 = jj309.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    if (ysxm.getKey().equals("30999")) {
                        jj30999 = jj30999.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    }
                } else if (ysxm.getKey().startsWith("310")) {
                    jj310 = jj310.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    if (ysxm.getKey().equals("31099")) {
                        jj31099 = jj31099.add(ysxm.getValue().stream().map(SourceImport::getJ).reduce(BigDecimal.ZERO, (a, b) -> a.add(b)));
                    }
                }
            }
            if (jj301.compareTo(BigDecimal.ZERO) > 0 && jj30199.divide(jj301, BigDecimal.ROUND_HALF_UP).compareTo(BigDecimal.valueOf(0.3)) > 0) {
                errMsg.append("科目" + kjkm.getKey().split("_")[1] + ":" + "经济分类支出30199超过30%\r\n");
            }
            if (jj302.compareTo(BigDecimal.ZERO) > 0 && jj30299.divide(jj302, BigDecimal.ROUND_HALF_UP).compareTo(BigDecimal.valueOf(0.3)) > 0) {
                errMsg.append("科目" + kjkm.getKey().split("_")[1] + ":" + "经济分类支出30299超过30%\r\n");
            }
            if (jj303.compareTo(BigDecimal.ZERO) > 0 && jj30399.divide(jj303, BigDecimal.ROUND_HALF_UP).compareTo(BigDecimal.valueOf(0.3)) > 0) {
                errMsg.append("科目" + kjkm.getKey().split("_")[1] + ":" + "经济分类支出30399超过30%\r\n");
            }
            if (jj309.compareTo(BigDecimal.ZERO) > 0 && jj30999.divide(jj309, BigDecimal.ROUND_HALF_UP).compareTo(BigDecimal.valueOf(0.3)) > 0) {
                errMsg.append("科目" + kjkm.getKey().split("_")[1] + ":" + "经济分类支出30999超过30%\r\n");
            }
            if (jj310.compareTo(BigDecimal.ZERO) > 0 && jj31099.divide(jj310, BigDecimal.ROUND_HALF_UP).compareTo(BigDecimal.valueOf(0.3)) > 0) {
                errMsg.append("科目" + kjkm.getKey().split("_")[1] + ":" + "经济分类支出31099超过30%\r\n");
            }
        }

        return errMsg.toString();
    }

    /**
     * 检查科目4601有没有发生额
     */
    public String checkKjkmExists(List<SourceImport> pzList) {
        StringBuilder errMsg = new StringBuilder();
        List<SourceImport> list = pzList.stream().filter(t -> t.getKjkm().startsWith("4601")).collect(Collectors.toList());
        if (org.apache.commons.collections4.CollectionUtils.isEmpty(list)) {
            errMsg.append("科目4601没有发生额\r\n");
        }

        return errMsg.toString();
    }

    /**
     * 检查饭堂收入
     */
    public String checkKjkmSrExists(List<SourceImport> pzList) {
        StringBuilder errMsg = new StringBuilder();
        List<SourceImport> list = pzList.stream().filter(t -> t.getKjkm().startsWith("100201") && t.getJ().compareTo(BigDecimal.ZERO) > 0 && t.getZy().contains("饭堂")).collect(Collectors.toList());
        if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(list)) {
            errMsg.append("科目100201有饭堂收入\r\n");
        }

        return errMsg.toString();
    }

    private SourceImport getRowData(SourceImport data, Row header, Row row) {
        String cell9 = POIExcelUtil.getString(row.getCell(9));//部门经济分类
        String cell10 = POIExcelUtil.getString(row.getCell(10));//财政拨款种类
        String cell11 = POIExcelUtil.getString(row.getCell(11));//财政指标
        String cell12 = POIExcelUtil.getString(row.getCell(12));//费用经济性质
        String cell13 = POIExcelUtil.getString(row.getCell(13));//往来单位
        String cell14 = POIExcelUtil.getString(row.getCell(14));//项目
        String cell15 = POIExcelUtil.getString(row.getCell(15));//预算项目
        String cell16 = POIExcelUtil.getString(row.getCell(16));//支出功能分类科目
        String cell17 = POIExcelUtil.getString(row.getCell(17));//资产类型
        String cell18 = POIExcelUtil.getString(row.getCell(18));//支付方式

        String header9 = POIExcelUtil.getString(header.getCell(9));//部门经济分类
        String header10 = POIExcelUtil.getString(header.getCell(10));//财政拨款种类
        String header11 = POIExcelUtil.getString(header.getCell(11));//财政指标
        String header12 = POIExcelUtil.getString(header.getCell(12));//费用经济性质
        String header13 = POIExcelUtil.getString(header.getCell(13));//往来单位
        String header14 = POIExcelUtil.getString(header.getCell(14));//项目
        String header15 = POIExcelUtil.getString(header.getCell(15));//预算项目
        String header16 = POIExcelUtil.getString(header.getCell(16));//支出功能分类科目
        String header17 = POIExcelUtil.getString(header.getCell(17));//资产类型
        String header18 = POIExcelUtil.getString(header.getCell(18));//支付方式

        data = switchCell(data, header9, cell9);
        data = switchCell(data, header10, cell10);
        data = switchCell(data, header11, cell11);
        data = switchCell(data, header12, cell12);
        data = switchCell(data, header13, cell13);
        data = switchCell(data, header14, cell14);
        data = switchCell(data, header15, cell15);
        data = switchCell(data, header16, cell16);
        data = switchCell(data, header17, cell17);
        data = switchCell(data, header18, cell18);
        return data;
    }

    private SourceImport switchCell(SourceImport data, String headerStr, String cellValue) {
        switch (headerStr) {
            case "部门支出经济分类":
                data.setBmjjfl(cellValue);
                break;
            case "财政拨款种类":
                data.setCzbkzl(cellValue);
                break;
            case "财政指标":
                data.setCzzb(cellValue);
                break;
            case "费用经济性质":
                data.setFylx(cellValue);
                break;
            case "往来单位":
                data.setWldw(cellValue);
                break;
            case "项目":
                data.setXm(cellValue);
                break;
            case "预算项目":
                data.setYsxm(cellValue);
                break;
            case "支出功能分类科目":
                data.setZcgnfl(cellValue);
                break;
            case "资产类型":
                data.setZclx(cellValue);
                break;
            case "支付方式":
                data.setJsfs(cellValue);
                break;
        }
        return data;
    }

}
